package de.ergin.fuhrparkv2.daten;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

	
public class DatenbankManager extends SQLiteOpenHelper {
		
		public static final String DB_NAME ="fuhrpark.db";
		private static final int DB_VERSION=1;
		private static final String FOREIGNKEY=
				"PRAGMA foreign_keys = ON";
		
		private static final String CAR_CREATE = 
				"CREATE TABLE fahrzeug (" +
				  "_id INTEGER PRIMARY KEY AUTOINCREMENT, "+
				  "kilometerstand INTEGER,"+
				  "_id_modell INTEGER REFERENCES modell(_id) ON DELETE SET NULL,"+
				  "_id_marke INTEGER REFERENCES marke(_id) ON DELETE SET NULL,"+
				  "kennzeichen VARCHAR(15) ,"+
				  "laufkilometer INTEGER ,"+
				  "wartungsintervall INTEGER,"+
				  "fahrzeugpreis DOUBLE,"+
				  "fahrzeugAktiv BOOLEAN DEFAULT 1 "+
				 ")" ;
		private static final String WARTUNG_CREATE = 
				"CREATE TABLE wartung (" +
				  "_id INTEGER PRIMARY KEY AUTOINCREMENT, "+
				  "_id_fahrzeug INTEGER REFERENCES fahrzeug(_id) ON DELETE SET NULL,"+
				  "oelwechsel BOOLEAN,"+
				  "filterwechsel BOOLEAN,"+
				  "zahnriemen BOOLEAN,"+
				  "reifen BOOLEAN,"+
				  "kilometerstand INTEGER DEFAULT 0,"+
				  "kosten DOUBLE,"+
				  "datum DATE"+
				")" ;
		
		private static final String MARKE_CREATE = 
				"CREATE TABLE marke (" +
				  "_id INTEGER PRIMARY KEY AUTOINCREMENT,"+
				  "marke VARCHAR(50) UNIQUE"+
				")" ;
		
		private static final String MODELL_CREATE = 
				"CREATE TABLE modell (" +
				  "_id INTEGER PRIMARY KEY AUTOINCREMENT, "+
				  "_id_marke INTEGER REFERENCES marke(_id) ON DELETE CASCADE,"+
				  "modell VARCHAR(50)"+
				")" ;
	
		
		private static final String CREATE_TRIGGER =
				"CREATE TRIGGER [update_on_wartung_kilomter] "+
				"AFTER INSERT ON [wartung] "+
				"FOR EACH ROW "+
				"WHEN (SELECT MAX(KILOMETERSTAND) FROM WARTUNG WHERE _ID_FAHRZEUG = NEW._ID_FAHRZEUG) >= NEW.KILOMETERSTAND "+
				"BEGIN "+
				"UPDATE fahrzeug set Laufkilometer=(select max(Kilometerstand) from wartung where _id_fahrzeug=new._id_fahrzeug) where _id = new._id_fahrzeug; "+
				"END";
		
		
		
		private static final String INIT_DATA=
				"INSERT INTO marke (_id,marke) VALUES(1,' ')";
		
		private static final String INIT_DATA2=
				"INSERT INTO marke (_id,marke) VALUES(2,'Sonstiges')";
				
		private static final String INIT_DATA3=
				"INSERT INTO modell (_id,_id_marke,modell) VALUES(1,1,' ')";
		
		private static final String INIT_DATA4=
				"INSERT INTO modell (_id,_id_marke,modell) VALUES(2,1,'Sonstiges')";
		
		private static final String CAR_DROP =
				"DROP TABLE IF EXISTS car";
		private static final String WARTUNG_DROP =
				 "DROP TABLE IF EXISTS wartung";
		private static final String MARKE_DROP =
				"DROP TABLE IF EXISTS marke";
		private static final String MODELL_DROP =
				 "DROP TABLE IF EXISTS modell";
		
		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL(CAR_CREATE);
			db.execSQL(WARTUNG_CREATE);
			db.execSQL(MODELL_CREATE);
			db.execSQL(MARKE_CREATE);
			
			db.execSQL(CREATE_TRIGGER);
			db.execSQL(FOREIGNKEY);
			db.execSQL(INIT_DATA);
			db.execSQL(INIT_DATA2);
			db.execSQL(INIT_DATA3);
			db.execSQL(INIT_DATA4);
			
			
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			db.execSQL(CAR_DROP);
			db.execSQL(WARTUNG_DROP);
			db.execSQL(MODELL_DROP);
			db.execSQL(MARKE_DROP);
			onCreate(db);
		}

		
		public DatenbankManager(Context context)
		{
			super(context,DB_NAME,null,DB_VERSION);
			
			
		}
		
/***********************************************************************************
 * Select Anweisungen  																				   *
 ***********************************************************************************/
		
		public static final String FAHRZEUGE_SELCET_RAW=			
				"select f._id,(ma.marke||' '||mo.modell)as marke,f.kennzeichen " +
				"from fahrzeug f,marke ma,modell mo " +
				"where f._id_marke=ma._id " +
				"and f._id_modell=mo._id " +
				"and FahrzeugAktiv=1";
		
		public static final String FAHRZEUGE_SELCET_RAW_BY_ID=			
				"select f._id,ma.marke,mo.modell,f.kennzeichen,f.kilometerstand,f.wartungsintervall,f.kennzeichen,f.fahrzeugpreis " +
				"from fahrzeug f,marke ma,modell mo " +
				"where f._id_marke=ma._id " +
				"and f._id_modell=mo._id " +
				"and FahrzeugAktiv=1 " + 
				"and f._id=? ";
		
		public static final String FAHRZEUGE_SELCET_WARTUNG=
				"SELECT w._id,f._id as id, f.laufkilometer, w.datum " +
				"FROM wartung w ,fahrzeug f " +
				"WHERE w._id_fahrzeug = f._id " +
				"and FahrzeugAktiv=1 " +
				"and w._id_fahrzeug=?";
		
		public static final String FAHRZEUG_DELETE=
				"UPDATE fahrzeug SET FahrzeugAktiv=0 WHERE _id = ?";
		
		public static final String MARKE_SELCET_RAW=
				"Select _id,Marke from marke";
		
		public static final String MODELL_SELCET_RAW=
				"Select _id,Modell from modell where _id_marke=1 or _id_marke=? ";
		
		public static final String WARTUNG_SELCET=
				//"Select _id,kilometerstand,datum from wartung where _id_fahrzeug=? order by _id DESC limit 0,5";
				"Select _id,(kilometerstand||' km') as kilometerstand,datum from wartung where _id_fahrzeug=? order by _id DESC limit 0,5";
		
		public static final String WARTUNG_SELCET_GRAPH=
				"Select _id,datum,kosten from wartung where _id_fahrzeug=? order by _id ASC";
		
		public static final String WARTUNG_DELETE=
				"DELETE FROM wartung where _id =?";
		
		public static final String WARTUNG_CHANGE=
				"Select _id,oelwechsel,filterwechsel,zahnriemen,reifen,kilometerstand,kosten,datum from wartung where _id=? ";
		
		
		
		
		
		
		
		
	

	        
}


